CREATE DATABASE Itacoffe_site;

 USE Itacoffe_site;

 CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) UNIQUE,
 password VARCHAR(255)
 );

CREATE TABLE coffe (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100),
 steps TEXT
 );

CREATE TABLE comments (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 coffe_id INT,
 comment TEXT,
 FOREIGN KEY (user_id) REFERENCES users(id),
 FOREIGN KEY (coffe_id) REFERENCES coffe(id)
 );

CREATE TABLE comment_log (
 log_id INT AUTO_INCREMENT PRIMARY KEY,
 comment_id INT,
 log_time TIMESTAMP DEFAULT 
CURRENT_TIMESTAMP
 );

 CREATE INDEX idx_user ON users(username);

INSERT INTO coffe (name, steps) VALUES 
('Espresso', '1. Grind fresh coffee beans\n2. Tamp grounds firmly\n3. Brew using espresso machine for 25–30 seconds'),
('Cappuccino', '1. Pull a double espresso shot\n2. Steam milk until creamy microfoam forms\n3. Pour milk over espresso and serve'),
('Latte', '1. Pull an espresso shot\n2. Steam milk to create light microfoam\n3. Combine milk with espresso and serve in a tall cup');


 INSERT INTO users (username, password) VALUES 
('bill', 'bill123'), 
('martin', 'martin123');

 DELIMITER //

CREATE PROCEDURE add_comment(IN uid INT, IN did 
INT, IN cmt TEXT)
 BEGIN
 INSERT INTO comments(user_id, dish_id, comment) 
VALUES (uid, did, cmt);

END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER after_comment_insert
 AFTER INSERT ON comments
 FOR EACH ROW
 BEGIN
 INSERT INTO comment_log(comment_id) VALUES 
(NEW.id);

 DELIMITER ;

 CALL add_comment(1, 2, 'Delicious!’);

SELECT * FROM comments;

CALL add_comment(2, 1, 'Needs more 
sugar.');

CALL add_comment(1, 3, 'Amazing.');

 SELECT * FROM comments;


